package com.ycdx.zwzc.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;

import com.ycdx.zwzc.pojo.Symptom;
import com.ycdx.zwzc.utils.DBPool;

public class SymptomImpl {
	static Logger logger = Logger.getLogger(SymptomImpl.class.getName());
	@SuppressWarnings("unchecked")
	public static List<Symptom> queryAllSymptom(){
		List<Symptom> symptomList = new ArrayList<Symptom>();
		String sql = "select * from tb_symptom order by type desc,id asc;";
		
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		try {
			symptomList = (List<Symptom>) qr.query(conn, sql, new BeanListHandler(Symptom.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("SymptomImpl.class-queryAllSymptom()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("SymptomImpl.class-queryAllSymptom()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return symptomList;
	}
	
	/**
	 * 根据type查询障碍现象
	 * @param type
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<Symptom> querySymptomByType(String type){
		List<Symptom> symptomList = new ArrayList<Symptom>();
		String sql = "select * from tb_symptom where type=?;";
		
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {type};
		try {
			symptomList = (List<Symptom>) qr.query(conn, sql, new BeanListHandler(Symptom.class),params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("SymptomImpl.class-querySymptomByType()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("SymptomImpl.class-querySymptomByType()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return symptomList;
	}
	
	/**
	 * 添加入库
	 * @param req
	 * @return
	 */
	public static int addSymptom(Symptom symptom){
		String sql = "insert into tb_symptom(" +
				"type,name) values(" +
				"?,?);";	
		
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {symptom.getType(),symptom.getName()};
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("SymptomImpl.class-addSymptom()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("SymptomImpl.class-addSymptom()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return flag;
	}
	
	/**
	 * 根据ID获取指定对象
	 * @param id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Symptom getSymptomById(String id){
		//System.out.println(id);
		String sql = "select * from tb_symptom where id=?";
		
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {id};
		Symptom symptom = new Symptom();
		try {
			symptom = (Symptom) qr.query(conn, sql, new BeanHandler(Symptom.class),params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("SymptomImpl.class-getSymptomById()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("SymptomImpl.class-getSymptomById()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return symptom;
	}
	
	/**
	 * 更新指定对象
	 * @param symptom
	 * @return
	 */
	public static int update(Symptom symptom){
		String sql = "update tb_symptom set type=?,name=? where id=?";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {symptom.getType(),symptom.getName(),symptom.getId()};
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("SymptomImpl.class-update()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("SymptomImpl.class-update()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return flag;
	}
	
	/**
	 * 删除指定对象
	 * @param symptom
	 * @return
	 */
	public static int deleteSymptom(String id){
		String sql = "delete from tb_symptom where id=?";
		
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {id};
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("SymptomImpl.class-deleteSymptom()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("SymptomImpl.class-deleteSymptom()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
}
